package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Product;
import com.ysd.util.DBManager;


public class Produc2tDaoImpl {
	public Product getProductList(int pId){
		
		Connection conn = DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		Product product = null;
		try {
			
			st = conn.createStatement();
			//通过产品Id查询产品
			rs =st.executeQuery("select * from product where productId="+pId);
			while (rs.next()) {
				
				//int string int int string
				int productId=rs.getInt("productId");
				String pName=rs.getString("productName");
				int productPrice=rs.getInt("productPrice");
				int salePrice=rs.getInt("salePrice");
				String imgs=rs.getString("imgs");
				String getMetails = rs.getString("getMetails");
				product = new Product(productId, pName, productPrice, salePrice,imgs,getMetails);
				
			}
			
			return product;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		}


	/**
	 * 多条件查询产品详情
	 * 为什么会返回list?因为在查询时可能会查询到多条数据
	 *
	 */
	public List<Product> getProductList(Product p,int page,int size){
		List<Product> productList=new ArrayList<Product>();
		
		Connection conn = DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		
		try {
			
			st = conn.createStatement();
			//多条件查询,条件有可能写也有可能不写,那么SQL语句不能写死
			String sql ="select p.*,pc.* from product p, productcategory pc where 1=1";//where 1=1 是个永真条件(永远为true),目的是为了解决拼接where的问题
			//非空判定
			if(p.getProductId()>0) {
				sql += " and productId ="+p.getProductId();
				
			}
			if(p.getProductName()!=null && !p.getProductName().equals("")) {
				sql += " and productName like '%"+p.getProductName()+"%'";
			}
			if(p.getBeginProductPrice()>0) {
				sql += " and productPrice>="+p.getBeginProductPrice();
			}
			if(p.getEndProductPrice()>0&&p.getBeginProductPrice()<p.getEndProductPrice()) {
				sql += " and productPrice<="+p.getEndProductPrice();
			}
			if(p.getBrand()!=null && !p.getBrand().equals("")) {
				sql += " and brand='"+p.getBrand()+"' ";
			}
			if(p.getProductColor()!=null && !p.getProductColor().equals("")) {
				sql += " and productColor like '%"+p.getProductColor()+"%'";
			}
			if(p.getGetMetails()!=null && !p.getGetMetails().equals("")) {
				sql += " and getMetails like '%"+p.getGetMetails()+"%'";
			}
			if(p.getProductCategoryId()!=0) {
				sql += " and p.productCategoryId="+ p.getProductCategoryId();
			}
			sql += " and p.productCategoryId=pc.productCategoryId";
			if(page>0) {
				sql+=" limit "+(page-1)*size+","+size;
			}
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while (rs.next()) {
				
				//int string int int string
				int productId=rs.getInt("productId");
				String pName=rs.getString("productName");
				String brand = rs.getString("brand");
				String productColor = rs.getString("productColor");
				int productCategoryId = rs.getInt("productCategoryId");
				String getMetails = rs.getString("getMetails");
				int productPrice=rs.getInt("productPrice");
				int salePrice=rs.getInt("salePrice");
				String imgs=rs.getString("imgs");
				String categoryName = rs.getString("productCategoryName");
				Product product=new Product(productId, pName, productPrice, salePrice, 0, 0, brand, productColor, productCategoryId, imgs, getMetails,categoryName);
				productList.add(product);
			}
			return productList;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public int getProductCount(Product p){
		int count = 0;
		
		Connection conn = DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		
		
		try {
			
			st = conn.createStatement();
			//多条件查询,条件有可能写也有可能不写,那么SQL语句不能写死
			String sql ="select count(*) from product p, productcategory pc where 1=1";//where 1=1 是个永真条件(永远为true),目的是为了解决拼接where的问题
			//非空判定
			if(p.getProductId()>0) {
				sql += " and productId ="+p.getProductId();
				
			}
			if(p.getProductName()!=null && !p.getProductName().equals("")) {
				sql += " and productName like '%"+p.getProductName()+"%'";
			}
			if(p.getBeginProductPrice()>0) {
				sql += " and productPrice>"+p.getBeginProductPrice();
			}
			if(p.getEndProductPrice()>0&&p.getBeginProductPrice()<p.getEndProductPrice()) {
				sql += " and productPrice<"+p.getEndProductPrice();
			}
			if(p.getBrand()!=null && !p.getBrand().equals("")) {
				sql += " and brand='"+p.getBrand()+"' ";
			}
			if(p.getProductColor()!=null && !p.getProductColor().equals("")) {
				sql += " and productColor like '%"+p.getProductColor()+"%'";
			}
			if(p.getGetMetails()!=null && !p.getGetMetails().equals("")) {
				sql += " and getMetails like '%"+p.getGetMetails()+"%'";
			}
			if(p.getProductCategoryId()!=0) {
				sql += " and p.productCategoryId="+ p.getProductCategoryId();
			}
			sql += " and p.productCategoryId=pc.productCategoryId";
			
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while (rs.next()) {
				count = rs.getInt("count(*)");
			}
			return count;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			return -1;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public int deleteProductDetailsCategoryId(int dpId) {
		return DBManager.executeUpdate("DELETE from product where productId ="+dpId);
	}
	
	public int addProduct(Product p) {
//		String sql = "insert into product values(null,'"+p.getProductName()+"',"+p.getProductPrice()+","+p.getSalePrice()+",'"+p.getBrand()+"','"+p.getProductColor()+"',"+p.getProductCategoryId()+",'"+p.getImgs()+"','"+p.getGetMetails()+"')";
		String sql = "insert into product values(null,'"+p.getProductName()+"',"+p.getProductPrice()+","+p.getSalePrice()+",'"+p.getBrand()+"','"+p.getProductColor()+"',"+p.getProductCategoryId()+",'"+p.getImgs()+"','"+p.getGetMetails()+"')";
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	/**
	 * 返回单个对象是因为结果集只有可能是一条
	 * @param pcId
	 * @return
	 */
	public Product getProductId(int pcId){
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Product p=null;
		try {
			st = conn.createStatement();
			String sql = "select * from product where productId="+pcId;
			rs = st.executeQuery(sql);
			System.out.println(sql);
			
			while(rs.next()) {
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				int productPrice = rs.getInt("productPrice");
				int salePrice = rs.getInt("salePrice");
				String brand = rs.getString("brand");
				String productColor = rs.getString("productColor");
				int productCategoryId = rs.getInt("productCategoryId");
				String imgs = rs.getString("imgs");
				String getMetails = rs.getString("getMetails");
				p = new Product(productId, productName, productPrice, salePrice, brand, productColor, productCategoryId, imgs, getMetails);
			}
			return p;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 增删改是返回受影响的行数
	 */
	public int updateProducts(Product p) {
		String sql = "update product set productName='"+p.getProductName()+"'," + 
				"productPrice="+p.getProductPrice()+"," + 
				"salePrice="+p.getSalePrice()+"," + 
				"brand = '"+p.getBrand()+"'," + 
				"productColor='"+p.getProductColor()+"'," + 
				"productCategoryId="+p.getProductCategoryId()+"," + 
				"imgs='"+p.getImgs()+"'," + 
				"getMetails='"+p.getGetMetails()+"'" + 
				" where productId="+p.getProductId();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	/**
	 * 批量删除
	 * @param prId
	 * @return
	 */
	public int deleteProId(String prId) {
		return DBManager.executeUpdate("delete from product where productId in("+prId+")");
	}
	
}

